﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;


namespace BaseFunction
{
    public class MsSql2008
    {
        /// <summary>
        /// 得到无限递归的SQL语句
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="id_TextField">id字段名</param>
        /// <param name="ParentID_TextField">父类字段名</param>
        /// <param name="ParentID">要得到递归子类的父类ID</param>
        /// <returns></returns>
        public static string GetRootNodeCTEsql(string TableName, string id_TextField, string ParentID_TextField, int ParentID)
        {
            string strSql = "";
            strSql = strSql + " Declare @" + id_TextField + " Int";
            strSql = strSql + " Set @" + id_TextField + "=" + ParentID + ";";
            strSql = strSql + " With RootNodeCTE(" + id_TextField + "," + ParentID_TextField + ")";
            strSql = strSql + " As";
            strSql = strSql + " (";
            strSql = strSql + " Select " + id_TextField + "," + ParentID_TextField + " From " + TableName + " Where " + ParentID_TextField + " In (@" + id_TextField + ") ";
            strSql = strSql + " Union All";
            strSql = strSql + " Select " + TableName + "." + id_TextField + "," + TableName + "." + ParentID_TextField + " From RootNodeCTE";
            strSql = strSql + " Inner Join " + TableName + "";
            strSql = strSql + " On RootNodeCTE." + id_TextField + " = " + TableName + "." + ParentID_TextField + "";
            strSql = strSql + " )";
            strSql = strSql + " Select * From RootNodeCTE";
            return strSql;
        }


        /// <summary>
        /// 调用分页存储过程
        /// </summary>
        /// <param name="SqlStr">查询语句</param>
        /// <param name="page">当前页码 从1开始</param>
        /// <param name="rows">每页显示N条记录</param>
        /// <returns></returns>
        public static DataSet ExecSqlPagerPROCEDURE(string SqlStr, int page, int rows)
        {
            SqlStr = "exec SqlPager '" + SqlStr + "'," + page + "," + rows;
            return BaseFunction.MsSql2008.Query(SqlStr);
        }
        /// <summary>
        /// 调用分页存储过程
        /// </summary>
        /// <param name="SqlStr">查询语句</param>
        /// <param name="page">当前页码 从1开始</param>
        /// <param name="rows">每页显示N条记录</param>
        /// <returns></returns>
        public static DataSet ExecSqlPagerPROCEDURE_YYMAll(string SqlStr, int page, int rows)
        {
            SqlStr = "exec SqlPager '" + SqlStr + "'," + page + "," + rows;
            return BaseFunction.MsSql2008.Query(SqlStr, ConfigurationManager.AppSettings["ConnectionStringYYMALL"]);
        }
        /// <summary>
        /// 调用分页存储过程
        /// </summary>
        /// <param name="SqlStr">查询语句</param>
        /// <param name="page">当前页码 从1开始</param>
        /// <param name="rows">每页显示N条记录</param>
        /// <returns></returns>
        public static DataSet ExecSqlPagerPROCEDURE(string Columns, string TableName, string Where, string OrderField, int page, int rows)
        {

            string SqlStr = "SELECT " + (Columns == "" ? "*" : Columns) + " FROM " + TableName;
            if (Where != "")
            {
                Where = Where.Replace("'", "''");
                SqlStr = SqlStr + " WHERE " + Where;
            }
            if (OrderField != "")
                SqlStr = SqlStr + " ORDER BY " + OrderField;
            return BaseFunction.MsSql2008.ExecSqlPagerPROCEDURE(SqlStr, page, rows);
        }




        /// <summary>
        /// 执行sql语句 返回受影响的行数
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static int ExecuteSql(string strSql)
        {
            YY.BLL.MainHelp m = new YY.BLL.MainHelp();
            return m.ExecuteSql(strSql);
        }


        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            YY.BLL.MainHelp m = new YY.BLL.MainHelp();
            return m.ExecuteSql(SQLString, cmdParms);
        }

        public static DataSet RunProcedure(string strSql, IDataParameter[] parameters)
        {
            YY.BLL.MainHelp m = new YY.BLL.MainHelp();
            return m.RunProcedure(strSql, parameters);
        }

        /// <summary>
        /// 执行sql语句 返回DataSet
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static DataSet Query(string strSql)
        {
            YY.BLL.MainHelp m = new YY.BLL.MainHelp();
            return m.Query(strSql);
        }

        /// <summary>
        /// 执行sql语句 返回DataSet 跨数据库操作 20151021
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static DataSet Query(string strSql,string connectionString)
        {
            YY.BLL.MainHelp m = new YY.BLL.MainHelp();
            return m.Query(strSql, connectionString);
        }



        /// <summary>
        /// 得到数据源 的指定列的值字符串,每行值将以逗号分隔
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="CloumName"></param>
        /// <returns></returns>
        public static ArrayList GetIdArray(DataSet ds, string CloumName)
        {
            ArrayList al = new ArrayList();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                al.Add(item[CloumName].ToString());
            }
            return al;
        }

        /// <summary>
        /// 根据指定的表名  获得该表的记录行数
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="Where"></param>
        /// <returns></returns>
        public static int GetSqlRowsCount(string TableName, string Where)
        {
            string sql = "select count(*) from " + TableName;
            if (Where != "")
            {
                sql += " where " + Where;
            }
            int n = int.Parse(Query(sql).Tables[0].Rows[0][0].ToString());
            return n;
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。0:执行失败   其他: sql语句执行次数
        /// </summary>
        /// <param name="SQLStringList"></param>
        /// <returns>0:执行失败   其他: sql语句执行次数</returns>
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            YY.BLL.MainHelp mainHelp = new YY.BLL.MainHelp();
            return mainHelp.ExecuteSqlTran(SQLStringList);
        }

        ///// <summary>
        ///// 执行多条SQL语句，实现数据库事务。 返回您所指定需要获取的IDENTITY ID集合
        ///// </summary>
        ///// <param name="SQLStringList"></param>
        ///// <returns></returns>
        //public static int ExecuteSqlTran(List<String> SQLStringList, out List<int> IDENTITYs)
        //{
        //    YY.BLL.MainHelp mainHelp = new YY.BLL.MainHelp();
        //    return mainHelp.ExecuteSqlTran(SQLStringList, out IDENTITYs);
        //}

        ///// <summary>
        ///// 执行多条SQL语句，实现数据库事务。0:执行失败   其他: sql语句执行次数
        ///// </summary>
        ///// <param name="SQLStringList"></param>
        ///// <returns>0:执行失败   其他: sql语句执行次数</returns>
        //public static int ExecuteSqlTran(List<String> SQLStringList, ref SqlConnection sqlconnection, ref SqlTransaction sqltransaction)
        //{
        //    YY.BLL.MainHelp mainHelp = new YY.BLL.MainHelp();
        //    return mainHelp.ExecuteSqlTran(SQLStringList, ref sqlconnection, ref sqltransaction);
        //}

        public static object GetSingle(string SQLString)
        {
            YY.BLL.MainHelp mainHelp = new YY.BLL.MainHelp();
            return mainHelp.GetSingle(SQLString);
        }
    }
}
